/*
 * Copyright (c) 2010 CCX(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCX(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCX(China) Co.,Ltd.
 *
 * Author: zhaosj
 * Date: 2010-10-27 下午06:45:59
 */
package com.ma.business;

import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.swing.filechooser.FileSystemView;

import jxl.Workbook;
import jxl.biff.DisplayFormat;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.ma.vo.TradeParameter;
import com.util.ConnUtil;

/**
 * 统计单只股票的资金和净利润（率）的变化； 统计每日总资金占用量和净利润（率）的变化；
 * 
 * @author zhaosj
 */
public class StatisticProfits {

	/**
	 * 统计单只股票资金和利润变化情况
	 */
	public void statisticSingleProfit(TradeParameter tp, String pathName) {

		try {

			Connection con = ConnUtil.getConnection("localOracle");
			String sql01 = "select * from ma_position order by innercode";
			PreparedStatement pst01 = con.prepareStatement(sql01);
			ResultSet rs01 = null;

			String dateStart = tp.getDateStart();
			String dateEnd = tp.getDateEnd();
			int bs = tp.getBs();
			int bl = tp.getBl();
			int ss = tp.getSs();
			int sl = tp.getSl();
			long quantity = tp.getQuantity();
			double br = tp.getBr();
			double sr = tp.getSr();
			double commision = tp.getCommision();
			double stampDuty = tp.getStampDuty();

			long innercode = 0;
			long amount = 0;
			double bankroll = 0;
			double newPrice = 0.0;
			String secucode = "";
			Date latestDay = null;

			WritableWorkbook wb = Workbook.createWorkbook(new File(pathName));
			WritableSheet ws0 = wb.createSheet("单只股票统计", 0);

			jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableCellFormat wcfFc = new WritableCellFormat(wfc);
			wcfFc.setVerticalAlignment(VerticalAlignment.JUSTIFY);// 垂直对齐方式为两端对齐
			wcfFc.setAlignment(Alignment.LEFT);
			wcfFc.setBorder(Border.ALL, BorderLineStyle.THIN);

			jxl.write.WritableFont wfc0 = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableCellFormat wcfFc0 = new WritableCellFormat(wfc0);
			wcfFc0.setBorder(Border.ALL, BorderLineStyle.THIN);

			DisplayFormat displayFormat = NumberFormats.PERCENT_FLOAT;
			WritableCellFormat wcfPercent = new WritableCellFormat(wfc, displayFormat);
			wcfPercent.setBorder(Border.ALL, BorderLineStyle.THIN);

			DisplayFormat df = DateFormats.DEFAULT;
			WritableCellFormat wcfDate = new WritableCellFormat(wfc, df);
			wcfDate.setBorder(Border.ALL, BorderLineStyle.THIN);

			// 统计单只股票 ============= start ==============
			ws0.mergeCells(0, 0, 2, 2);
			ws0.mergeCells(3, 0, 4, 2);
			ws0.mergeCells(5, 0, 6, 2);

			for (int i = 1; i < 10; i++)
				ws0.setColumnView(i, 13);

			String title01 = "日期：" + dateStart + " 至 " + dateEnd + "\n买入均线天数：短" + bs + "天，长" + bl + "天" + "\n卖出均线天数：短" + ss + "天，长" + sl + "天";
			Label label = new Label(0, 0, title01, wcfFc);
			ws0.addCell(label);

			title01 = "原始资金：" + quantity + "元" + "\n买入比例：" + br * 100 + "%" + "\n卖出比例：" + sr * 100 + "%";
			label = new Label(3, 0, title01, wcfFc);
			ws0.addCell(label);

			title01 = "佣金(买卖)：" + commision * 100 + "%\n印花税(卖)：" + stampDuty * 100 + "%";
			label = new Label(5, 0, title01, wcfFc);
			ws0.addCell(label);

			ws0.addCell(new Label(0, 3, "序号", wcfFc0));
			ws0.addCell(new Label(1, 3, "股票代码", wcfFc0));
			ws0.addCell(new Label(2, 3, "市值(元)", wcfFc0));
			ws0.addCell(new Label(3, 3, "可用资金(元)", wcfFc0));
			ws0.addCell(new Label(4, 3, "净利润(元)", wcfFc0));
			ws0.addCell(new Label(5, 3, "利润率", wcfFc0));
			ws0.addCell(new Label(6, 3, "最终交易日期", wcfFc0));

			ws0.getSettings().setVerticalFreeze(4); // 锁定前4行，不允许滚动

			rs01 = pst01.executeQuery();

			String sql02 = "select closeprice from ma_stock where innercode = ? and tradingday = (select max(tradingday) from ma_stock where innercode = ?)";
			PreparedStatement pst02 = con.prepareStatement(sql02);
			int i = 0;
			while (rs01.next()) {
				i++;
				innercode = rs01.getLong("innercode");

				amount = rs01.getLong("amount");
				bankroll = rs01.getDouble("bankroll");
				secucode = rs01.getString("secucode");
				latestDay = (java.sql.Date) rs01.getDate("tradingday");

				System.out.println("Statistic stock ：" + secucode);

				pst02.setLong(1, innercode);
				pst02.setLong(2, innercode);
				ResultSet rs02 = pst02.executeQuery();
				if (rs02 != null && rs02.next()) {
					newPrice = rs02.getDouble("closeprice");

				}
				double marketValue = amount * newPrice; // 市值
				rs02.close();

				// insert into excel doc...
				ws0.addCell(new jxl.write.Number(0, i + 3, i, wcfFc)); // 序号
				ws0.addCell(new Label(1, i + 3, secucode, wcfFc)); // 股票代码
				ws0.addCell(new jxl.write.Number(2, i + 3, marketValue, wcfFc));// 市值
				ws0.addCell(new jxl.write.Number(3, i + 3, bankroll, wcfFc));

				double netProfits = marketValue + bankroll - quantity; // 净利润
				ws0.addCell(new jxl.write.Number(4, i + 3, netProfits, wcfFc));

				double npr = (new BigDecimal(netProfits / quantity * 1.0).setScale(4, BigDecimal.ROUND_HALF_UP)).doubleValue();
				ws0.addCell(new jxl.write.Number(5, i + 3, npr, wcfPercent)); // 利润率
				ws0.addCell(new DateTime(6, i + 3, latestDay, wcfDate));

			}
			// 统计单只股票 ============= end ==============

			wb.write();
			wb.close();

			rs01.close();
			pst01.close();
			pst02.close();
			con.close();

		} catch (Exception e) {

			e.printStackTrace();
		}

	}

	/**
	 * 统计累计数据
	 * 
	 * @param tp
	 * @param pathName
	 */
	public void statisticTotalProfits(TradeParameter tp, String pathName) {
		try {

			String dateStart = tp.getDateStart();
			String dateEnd = tp.getDateEnd();
			int bs = tp.getBs();
			int bl = tp.getBl();
			int ss = tp.getSs();
			int sl = tp.getSl();
			long quantity = tp.getQuantity();
			double br = tp.getBr();
			double sr = tp.getSr();
			double commision = tp.getCommision();
			double stampDuty = tp.getStampDuty();

			Connection con = ConnUtil.getConnection("localOracle");
			String sql01 = "select distinct(tradingday) from ma_history order by 1";
			PreparedStatement pst01 = con.prepareStatement(sql01);
			ResultSet rs01 = pst01.executeQuery();

			Workbook _wb = Workbook.getWorkbook(new File(pathName));
			WritableWorkbook wb = Workbook.createWorkbook(new File(pathName), _wb);
			WritableSheet ws1 = wb.createSheet("累计统计", 1);

			jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			jxl.write.WritableCellFormat wcfFc = new jxl.write.WritableCellFormat(wfc);
			wcfFc.setVerticalAlignment(VerticalAlignment.JUSTIFY);// 垂直对齐方式为两端对齐
			wcfFc.setAlignment(Alignment.LEFT);
			wcfFc.setBorder(Border.ALL, BorderLineStyle.THIN);

			jxl.write.WritableFont wfc0 = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			jxl.write.WritableCellFormat wcfFc0 = new jxl.write.WritableCellFormat(wfc0);
			wcfFc0.setBorder(Border.ALL, BorderLineStyle.THIN);

			DisplayFormat displayFormat = NumberFormats.PERCENT_FLOAT;
			WritableCellFormat wcfPercent = new WritableCellFormat(wfc, displayFormat);
			wcfPercent.setBorder(Border.ALL, BorderLineStyle.THIN);

			DisplayFormat df = DateFormats.DEFAULT;
			WritableCellFormat wcfDate = new WritableCellFormat(wfc, df);
			wcfDate.setBorder(Border.ALL, BorderLineStyle.THIN);

			// 合并单元格
			ws1.mergeCells(0, 0, 2, 2);
			ws1.mergeCells(3, 0, 5, 2);
			ws1.mergeCells(6, 0, 7, 2);

			for (int i = 0; i < 10; i++)
				ws1.setColumnView(i, 15);

			String title01 = "日期：" + dateStart + " 至 " + dateEnd + "\n买入均线天数：短" + bs + "天，长" + bl + "天" + "\n卖出均线天数：短" + ss + "天，长" + sl + "天";
			Label label = new Label(0, 0, title01, wcfFc);
			ws1.addCell(label);

			title01 = "原始资金：" + quantity + "元" + "\n买入比例：" + br * 100 + "%" + "\n卖出比例：" + sr * 100 + "%";
			label = new Label(3, 0, title01, wcfFc);
			ws1.addCell(label);

			title01 = "佣金(买卖)：" + commision * 100 + "%\n印花税(卖)：" + stampDuty * 100 + "%";
			label = new Label(6, 0, title01, wcfFc);
			ws1.addCell(label);

			ws1.addCell(new Label(0, 3, "日期", wcfFc0));
			ws1.addCell(new Label(1, 3, "买入资金", wcfFc0));
			ws1.addCell(new Label(2, 3, "卖出资金", wcfFc0));
			ws1.addCell(new Label(3, 3, "持有资金", wcfFc0));
			ws1.addCell(new Label(4, 3, "可用资金", wcfFc0));
			ws1.addCell(new Label(5, 3, "净利润", wcfFc0));
			ws1.addCell(new Label(6, 3, "利润率", wcfFc0));
			ws1.addCell(new Label(7, 3, "股票种类(只)", wcfFc0));
			ws1.getSettings().setVerticalFreeze(4); // 锁定前4行，不允许滚动

			Date tradingday = null;
			String sql02 = "select * from ma_history where tradingday = ? ";
			PreparedStatement pst02 = con.prepareStatement(sql02);
			ResultSet rs02 = null;

			// 同一天内，一只股票如果同时存在买入和卖出记录，只计数一次
			String sql03 = "select count(*) from (select distinct innercode from ma_history where tradingday = ?)";
			PreparedStatement pst03 = con.prepareStatement(sql03);
			ResultSet rs03 = null;

			int rowNum = 3; // 记录个数，每天一条
			while (rs01.next()) {
				rowNum++;

				double totalBuyBankroll = 0.0; // 买入资金
				double totalSellBankroll = 0.0; // 卖出资金
				double totalHoldBankroll = 0.0; // 持有资金
				double totalUsefulBankroll = 0.0; // 可用资金

				long amount = 0;
				long volume = 0;
				double price = 0.0;
				double bankroll = 0.0;
				String dealing = "";

				tradingday = rs01.getDate("tradingday");

				int stockNumber = 0; // 记录条数（当日交易的股票个数）
				pst03.setDate(1, (java.sql.Date) tradingday);
				rs03 = pst03.executeQuery();
				if (rs03.next())
					stockNumber = rs03.getInt(1);

				pst02.setDate(1, (java.sql.Date) tradingday);
				rs02 = pst02.executeQuery();

				System.out.println("Statistic total stock data：" + tradingday);

				while (rs02.next()) {
					amount = rs02.getLong("amount");
					volume = rs02.getLong("volume");
					price = rs02.getDouble("price");
					bankroll = rs02.getDouble("bankroll");
					dealing = rs02.getString("dealing");

					totalUsefulBankroll += bankroll;
					totalHoldBankroll += amount * price;

					if ("B".equals(dealing.toUpperCase())) {
						totalBuyBankroll += volume * price;

					} else if ("S".equals(dealing.toUpperCase())) {
						totalSellBankroll += volume * price;

					} else {
						System.out.println("Trade history error：id = " + rs02.getLong("id"));
					}
				}
				double totalNetProfits = totalHoldBankroll + totalUsefulBankroll - quantity * stockNumber; // 净利润
				double totalNetProfitsRate = totalNetProfits / (quantity * stockNumber * 1.0); // 利润率

				ws1.addCell(new DateTime(0, rowNum, tradingday, wcfDate)); // 日期
				ws1.addCell(new jxl.write.Number(1, rowNum, totalBuyBankroll, wcfFc)); // 买入资金
				ws1.addCell(new jxl.write.Number(2, rowNum, totalSellBankroll, wcfFc)); // 卖出资金
				ws1.addCell(new jxl.write.Number(3, rowNum, totalHoldBankroll, wcfFc)); // 持有资金
				ws1.addCell(new jxl.write.Number(4, rowNum, totalUsefulBankroll, wcfFc)); // 可用资金
				ws1.addCell(new jxl.write.Number(5, rowNum, totalNetProfits, wcfFc)); // 净利润
				ws1.addCell(new jxl.write.Number(6, rowNum, totalNetProfitsRate, wcfPercent)); // 利润率
				ws1.addCell(new jxl.write.Number(7, rowNum, stockNumber, wcfFc)); // 股票

				rs02.close();
			}

			pst02.close();
			rs01.close();
			pst01.close();
			con.close();

			wb.write();
			wb.close();
			_wb.close();

		} catch (Exception e) {

			e.printStackTrace();
		}
	}

	/**
	 * 统计交易历史
	 * 
	 * @param tp
	 * @param pathName
	 */
	public void statisticTradeHistory(TradeParameter tp, String pathName) {

		try {

			String dateStart = tp.getDateStart(); // 起始日期
			String dateEnd = tp.getDateEnd(); // 终止日期
			int bs = tp.getBs(); // 买入短线天数
			int bl = tp.getBl(); // 买入长线天数
			int ss = tp.getSs(); // 卖出短线天数
			int sl = tp.getSl(); // 卖出长线天数
			long quantity = tp.getQuantity(); // 原始资金量
			double br = tp.getBr(); // 买入资金比例
			double sr = tp.getSr(); // 卖出股票比例
			double commision = tp.getCommision(); // 佣金
			double stampDuty = tp.getStampDuty(); // 印花税

			Connection con = ConnUtil.getConnection("localOracle");
			String sql01 = "select * from ma_history order by innercode,tradingday ";
			PreparedStatement pst01 = con.prepareStatement(sql01);
			ResultSet rs01 = pst01.executeQuery();

			Workbook _wb = Workbook.getWorkbook(new File(pathName));
			WritableWorkbook wb = Workbook.createWorkbook(new File(pathName), _wb);
			WritableSheet ws2 = wb.createSheet("交易历史", 2);

			// 正常字体，有边框
			jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			jxl.write.WritableCellFormat wcfFc = new jxl.write.WritableCellFormat(wfc);
			wcfFc.setVerticalAlignment(VerticalAlignment.JUSTIFY);// 垂直对齐方式为两端对齐
			wcfFc.setAlignment(Alignment.LEFT);
			wcfFc.setBorder(Border.ALL, BorderLineStyle.THIN);

			// 黑体，有边框
			jxl.write.WritableFont wfc0 = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			jxl.write.WritableCellFormat wcfFc0 = new jxl.write.WritableCellFormat(wfc0);
			wcfFc0.setBorder(Border.ALL, BorderLineStyle.THIN);
			DisplayFormat displayFormat = NumberFormats.PERCENT_FLOAT;
			WritableCellFormat wcfPercent = new WritableCellFormat(wfc, displayFormat);
			wcfPercent.setBorder(Border.ALL, BorderLineStyle.THIN);

			// 正常字体，有边框，按照日期格式显示
			DisplayFormat df = DateFormats.DEFAULT;
			WritableCellFormat wcfDate = new WritableCellFormat(wfc, df);
			wcfDate.setBorder(Border.ALL, BorderLineStyle.THIN);

			// 合并单元格
			ws2.mergeCells(0, 0, 2, 2);
			ws2.mergeCells(3, 0, 4, 2);
			ws2.mergeCells(5, 0, 6, 2);

			// 设置列宽度15
			for (int i = 1; i < 10; i++)
				ws2.setColumnView(i, 15);

			String title01 = "日期：" + dateStart + " 至 " + dateEnd + "\n买入均线天数：短" + bs + "天，长" + bl + "天" + "\n卖出均线天数：短" + ss + "天，长" + sl + "天";
			Label label = new Label(0, 0, title01, wcfFc);
			ws2.addCell(label);

			title01 = "原始资金：" + quantity + "元" + "\n买入比例：" + br * 100 + "%" + "\n卖出比例：" + sr * 100 + "%";
			label = new Label(3, 0, title01, wcfFc);
			ws2.addCell(label);

			title01 = "佣金(买卖)：" + commision * 100 + "%\n印花税(卖)：" + stampDuty * 100 + "%";
			label = new Label(5, 0, title01, wcfFc);
			ws2.addCell(label);

			ws2.addCell(new Label(0, 3, "序号", wcfFc0));
			ws2.addCell(new Label(1, 3, "股票代码", wcfFc0));
			ws2.addCell(new Label(2, 3, "买卖方向", wcfFc0));
			ws2.addCell(new Label(3, 3, "交易价格", wcfFc0));
			ws2.addCell(new Label(4, 3, "交易数量", wcfFc0));
			ws2.addCell(new Label(5, 3, "现持有量", wcfFc0));
			ws2.addCell(new Label(6, 3, "交易日期", wcfFc0));

			ws2.getSettings().setVerticalFreeze(4); // 锁定前4行，不允许滚动

			Date tradingday = null; // 交易日期
			long amount = 0; // 持有量
			long volume = 0; // 交易数量
			double price = 0.0; // 交易价格
			String dealing = ""; // 买卖方向
			String secucode = ""; // 股票代码

			int rowNum = 0; // 交易条数
			while (rs01.next()) {
				rowNum++;

				tradingday = rs01.getDate("tradingday");
				amount = rs01.getLong("amount");
				volume = rs01.getLong("volume");
				price = rs01.getDouble("price");
				secucode = rs01.getString("secucode");
				dealing = rs01.getString("dealing");
				if ("B".equals(dealing.toUpperCase()))
					dealing = "买入";
				else
					dealing = "卖出";

				ws2.addCell(new jxl.write.Number(0, rowNum + 3, rowNum, wcfFc));// 序号
				ws2.addCell(new Label(1, rowNum + 3, secucode, wcfFc)); // 股票
				ws2.addCell(new Label(2, rowNum + 3, dealing, wcfFc)); // 买卖方向
				ws2.addCell(new jxl.write.Number(3, rowNum + 3, price, wcfFc)); // 买卖价格
				ws2.addCell(new jxl.write.Number(4, rowNum + 3, volume, wcfFc)); // 交易数量
				ws2.addCell(new jxl.write.Number(5, rowNum + 3, amount, wcfFc)); // 现持有量
				ws2.addCell(new DateTime(6, rowNum + 3, tradingday, wcfDate)); // 日期
				
			}

			rs01.close();
			pst01.close();
			con.close();
			wb.write();
			wb.close();
			_wb.close();

		} catch (Exception e) {

			e.printStackTrace();
		}
	}

	/**
	 * 统计
	 * 
	 * @param tp
	 */
	public void statisticProfits(TradeParameter tp) {

		// 获取桌面路径
		FileSystemView fsv = FileSystemView.getFileSystemView();
		File root = fsv.getHomeDirectory();

		String maPath = root.getPath() + "\\MA\\";
		File ma = new File(maPath);
		if (!ma.exists())
			ma.mkdirs();

		String aStringVar = "_" + tp.getBs() + "_" + tp.getBl() + "_" + tp.getSs() + "_" + tp.getSl() + "_" + tp.getBr() + "_" + tp.getSr() + "_";
		String pathName = maPath + "收益率统计结果_" + tp.getDateStart() + "—" + tp.getDateEnd() + aStringVar + tp.getQuantity() / 10000 + "万.xls";

		// 单只股票统计
		statisticSingleProfit(tp, pathName);

		// 累计统计
		statisticTotalProfits(tp, pathName);

		// 交易历史
		statisticTradeHistory(tp, pathName);

	}

	public static void main(String[] args) {

		// 获取桌面路径
		FileSystemView fsv = FileSystemView.getFileSystemView();
		File root = fsv.getHomeDirectory();

		String maPath = root.getPath() + "\\MA\\";
		File ma = new File(maPath);
		if (!ma.exists())
			ma.mkdirs();

		StatisticProfits sp = new StatisticProfits();

		TradeParameter tp = new TradeParameter();
		tp.setBs(5);
		tp.setBl(10);
		tp.setSs(5);
		tp.setSl(10);
		tp.setQuantity(1000000);
		tp.setBr(0.1);
		tp.setSr(0.5);
		tp.setDateEnd("2010-02-02");
		tp.setDateStart("2010-10-01");
		tp.setCommision(0.003);
		tp.setStampDuty(0.003);

		String dateStr = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss").format(new Date());
		String pathName = maPath + "收益率统计结果_" + dateStr + ".xls";

		sp.statisticSingleProfit(tp, pathName);
		sp.statisticTotalProfits(tp, pathName);
	}

}
